<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Upsert - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span></li>
<li class="tocentry"><a href="SingleModelCrudAsync.htm">Async Operations</a>
</li>
<li class="tocentry"><a href="LargeBatch.htm">Batch Inserts with Large Collections</a>
</li>
<li class="tocentry"><a href="BulkInsert.htm">Bulk Inserts</a>
</li>
<li class="tocentry"><a href="ConnectionSharing.htm">Connection Sharing</a>
</li>
<li class="tocentry"><a href="MultipleDB.htm">Multiple Databases</a>
</li>
<li class="tocentry"><a href="PartialUpdate.htm">Partial Updates</a>
</li>
<li class="tocentry"><a href="Transactions.htm">Transactions</a>
</li>
<li class="tocentry current"><a class="current" href="Upsert.htm">Upsert</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="Advancedscenarios.htm">Advanced scenarios</a></li> / <li><a href="Upsert.htm">Upsert</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="upsert">Upsert<a class="headerlink" href="#upsert" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>These scenarios demonstrate how to perform an upsert (insert/update).</p>
<p>Where possible, this should be performed in a single statement.</p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IUpsertScenario&lt;TDivision&gt;
   where TDivision : class, IDivision, new()
{
    /// &lt;summary&gt;
    /// Gets an Division row by its primary key.
    /// &lt;/summary&gt;
    TDivision? GetByKey(int divisionKey);

    /// &lt;summary&gt;
    /// Performs an upsert where it looks for a matching row by DivisionName instead of the primary key.
    /// DivisionKey may be 0.
    /// &lt;/summary&gt;
    /// &lt;returns&gt;The DivisionKey of the inserted or updated row&lt;/returns&gt;
    int UpsertByName(TDivision division);

    /// &lt;summary&gt;
    /// Performs an upsert where a 0 for DivisionKey means an insert.
    /// &lt;/summary&gt;
    /// &lt;returns&gt;The DivisionKey of the inserted or updated row&lt;/returns&gt;
    int UpsertByPrimaryKey(TDivision division);
}
</code></pre>

<h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>This code demonstrates the MERGE syntax.</p>
<pre><code class="cs">    public class UpsertScenario : SqlServerScenarioBase, IUpsertScenario&lt;Division&gt;
    {
        public UpsertScenario(string connectionString) : base(connectionString)
        { }

        public Division GetByKey(int divisionKey)
        {
            const string sql = @&quot;SELECT d.DivisionKey,
       d.DivisionId,
       d.DivisionName,
       d.CreatedDate,
       d.ModifiedDate,
       d.CreatedByEmployeeKey,
       d.ModifiedByEmployeeKey,
       d.SalaryBudget,
       d.FteBudget,
       d.SuppliesBudget,
       d.FloorSpaceBudget,
       d.MaxEmployees,
       d.LastReviewCycle,
       d.StartTime FROM HR.Division d WHERE d.DivisionKey = @DivisionKey;&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@DivisionKey&quot;, divisionKey);
                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();
                    return new Division(reader);
                }
            }
        }

        public int UpsertByName(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

            const string sql = @&quot;MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionName = source.DivisionName
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;&quot;;

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@DivisionKey&quot;, division.DivisionKey);
                cmd.Parameters.AddWithValue(&quot;@DivisionName&quot;, division.DivisionName);
                cmd.Parameters.AddWithValue(&quot;@DivisionId&quot;, division.DivisionId);
                cmd.Parameters.AddWithValue(&quot;@ModifiedDate&quot;, division.ModifiedDate);
                cmd.Parameters.AddWithValue(&quot;@CreatedByEmployeeKey&quot;, division.CreatedByEmployeeKey);
                cmd.Parameters.AddWithValue(&quot;@ModifiedByEmployeeKey&quot;, division.ModifiedByEmployeeKey);
                cmd.Parameters.AddWithValue(&quot;@FteBudget&quot;, (object?)division.FteBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@SuppliesBudget&quot;, (object?)division.SuppliesBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@FloorSpaceBudget&quot;, (object?)division.FloorSpaceBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@MaxEmployees&quot;, (object?)division.MaxEmployees ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@LastReviewCycle&quot;, (object?)division.LastReviewCycle ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@StartTime&quot;, (object?)division.StartTime ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@SalaryBudget&quot;, (object?)division.SalaryBudget ?? DBNull.Value);

                return (int)cmd.ExecuteScalar();
            }
        }

        public int UpsertByPrimaryKey(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

            const string sql = @&quot;MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionKey = source.DivisionKey
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               DivisionName = source.DivisionName,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;&quot;;

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@DivisionKey&quot;, division.DivisionKey);
                cmd.Parameters.AddWithValue(&quot;@DivisionName&quot;, division.DivisionName);
                cmd.Parameters.AddWithValue(&quot;@DivisionId&quot;, division.DivisionId);
                cmd.Parameters.AddWithValue(&quot;@ModifiedDate&quot;, division.ModifiedDate);
                cmd.Parameters.AddWithValue(&quot;@CreatedByEmployeeKey&quot;, division.CreatedByEmployeeKey);
                cmd.Parameters.AddWithValue(&quot;@ModifiedByEmployeeKey&quot;, division.ModifiedByEmployeeKey);
                cmd.Parameters.AddWithValue(&quot;@FteBudget&quot;, (object?)division.FteBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@SuppliesBudget&quot;, (object?)division.SuppliesBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@FloorSpaceBudget&quot;, (object?)division.FloorSpaceBudget ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@MaxEmployees&quot;, (object?)division.MaxEmployees ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@LastReviewCycle&quot;, (object?)division.LastReviewCycle ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@StartTime&quot;, (object?)division.StartTime ?? DBNull.Value);
                cmd.Parameters.AddWithValue(&quot;@SalaryBudget&quot;, (object?)division.SalaryBudget ?? DBNull.Value);

                return (int)cmd.ExecuteScalar();
            }
        }
    }
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;Division&gt;
{
    readonly SqlServerDataSource m_DataSource;

    public UpsertScenario(SqlServerDataSource dataSource)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), $&quot;{nameof(dataSource)} is null.&quot;);

        //Auto-populate the ModifiedDate column
        m_DataSource = dataSource.WithRules(
            new DateTimeRule(&quot;ModifiedDate&quot;, DateTimeKind.Utc, OperationTypes.Update)
            );
    }

    public Division GetByKey(int divisionKey)
    {
        return m_DataSource.GetByKey&lt;Division&gt;(divisionKey).ToObject().Execute()!;
    }

    public int UpsertByName(Division division)
    {
        //WithKeys indicates that we're matching on something other than the primary key
        return m_DataSource.Upsert(division).WithKeys(&quot;DivisionName&quot;).ToInt32().Execute();
    }

    public int UpsertByPrimaryKey(Division division)
    {
        return m_DataSource.Upsert(division).ToInt32().Execute();
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">    public class UpsertScenario : ScenarioBase, IUpsertScenario&lt;Division&gt;
    {
        public UpsertScenario(string connectionString) : base(connectionString)
        { }

        public Division GetByKey(int divisionKey)
        {
            const string sql = @&quot;SELECT d.DivisionKey,
       d.DivisionId,
       d.DivisionName,
       d.CreatedDate,
       d.ModifiedDate,
       d.CreatedByEmployeeKey,
       d.ModifiedByEmployeeKey,
       d.SalaryBudget,
       d.FteBudget,
       d.SuppliesBudget,
       d.FloorSpaceBudget,
       d.MaxEmployees,
       d.LastReviewCycle,
       d.StartTime FROM HR.Division d WHERE d.DivisionKey = @DivisionKey;&quot;;
            using (var con = OpenConnection())
                return con.QuerySingle&lt;Division&gt;(sql, new { divisionKey });
        }

        public int UpsertByName(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

            const string sql = @&quot;MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionName = source.DivisionName
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;&quot;;

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
                return con.ExecuteScalar&lt;int&gt;(sql, division);
        }

        public int UpsertByPrimaryKey(Division division)
        {
            if (division == null)
                throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

            const string sql = @&quot;MERGE INTO HR.Division target
USING
(
    VALUES
        (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
         @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
         @LastReviewCycle, @StartTime
        )
) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
          ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
          LastReviewCycle, StartTime
         )
ON target.DivisionKey = source.DivisionKey
WHEN MATCHED THEN
    UPDATE SET DivisionId = source.DivisionId,
               DivisionName = source.DivisionName,
               ModifiedDate = source.ModifiedDate,
               ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
               SalaryBudget = source.SalaryBudget,
               FteBudget = source.FteBudget,
               SuppliesBudget = source.SuppliesBudget,
               FloorSpaceBudget = source.FloorSpaceBudget,
               MaxEmployees = source.MaxEmployees,
               LastReviewCycle = source.LastReviewCycle,
               StartTime = source.StartTime
WHEN NOT MATCHED THEN
    INSERT
    (
        DivisionId,
        DivisionName,
        CreatedByEmployeeKey,
        ModifiedByEmployeeKey,
        SalaryBudget,
        FteBudget,
        SuppliesBudget,
        FloorSpaceBudget,
        MaxEmployees,
        LastReviewCycle,
        StartTime
    )
    VALUES
    (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
     source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
     source.LastReviewCycle, source.StartTime)
OUTPUT Inserted.DivisionKey;&quot;;

            //update audit column
            division.ModifiedDate = DateTime.UtcNow;

            using (var con = OpenConnection())
                return con.ExecuteScalar&lt;int&gt;(sql, division);
        }
    }
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class UpsertScenario : ScenarioBase, IUpsertScenario&lt;Division&gt;
{
    public UpsertScenario(string connectionString) : base(connectionString)
    { }

    public Division GetByKey(int divisionKey)
    {
        const string sql = @&quot;SELECT d.DivisionKey,
           d.DivisionId,
           d.DivisionName,
           d.CreatedDate,
           d.ModifiedDate,
           d.CreatedByEmployeeKey,
           d.ModifiedByEmployeeKey,
           d.SalaryBudget,
           d.FteBudget,
           d.SuppliesBudget,
           d.FloorSpaceBudget,
           d.MaxEmployees,
           d.LastReviewCycle,
           d.StartTime FROM HR.Division d WHERE d.DivisionKey = @divisionKey;&quot;;

        return DbConnector.ReadSingle&lt;Division&gt;(sql, new { divisionKey }).Execute();
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        const string sql = @&quot;MERGE INTO HR.Division target
            USING
            (
                VALUES
                    (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
                     @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
                     @LastReviewCycle, @StartTime
                    )
            ) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
                      ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
                      LastReviewCycle, StartTime
                     )
            ON target.DivisionName = source.DivisionName
            WHEN MATCHED THEN
                UPDATE SET DivisionId = source.DivisionId,
                           ModifiedDate = source.ModifiedDate,
                           ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
                           SalaryBudget = source.SalaryBudget,
                           FteBudget = source.FteBudget,
                           SuppliesBudget = source.SuppliesBudget,
                           FloorSpaceBudget = source.FloorSpaceBudget,
                           MaxEmployees = source.MaxEmployees,
                           LastReviewCycle = source.LastReviewCycle,
                           StartTime = source.StartTime
            WHEN NOT MATCHED THEN
                INSERT
                (
                    DivisionId,
                    DivisionName,
                    CreatedByEmployeeKey,
                    ModifiedByEmployeeKey,
                    SalaryBudget,
                    FteBudget,
                    SuppliesBudget,
                    FloorSpaceBudget,
                    MaxEmployees,
                    LastReviewCycle,
                    StartTime
                )
                VALUES
                (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
                 source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
                 source.LastReviewCycle, source.StartTime)
            OUTPUT Inserted.DivisionKey;&quot;;

        //update audit column
        var utcNow = DateTime.UtcNow;
        division.CreatedDate = utcNow;
        division.ModifiedDate = utcNow;

        return DbConnector.Scalar&lt;int&gt;(sql, division).Execute();
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        const string sql = @&quot;MERGE INTO HR.Division target
            USING
            (
                VALUES
                    (@DivisionKey, @DivisionId, @DivisionName, @ModifiedDate, @CreatedByEmployeeKey,
                     @ModifiedByEmployeeKey, @SalaryBudget, @FteBudget, @SuppliesBudget, @FloorSpaceBudget, @MaxEmployees,
                     @LastReviewCycle, @StartTime
                    )
            ) source (DivisionKey, DivisionId, DivisionName, ModifiedDate, CreatedByEmployeeKey,
                      ModifiedByEmployeeKey, SalaryBudget, FteBudget, SuppliesBudget, FloorSpaceBudget, MaxEmployees,
                      LastReviewCycle, StartTime
                     )
            ON target.DivisionKey = source.DivisionKey
            WHEN MATCHED THEN
                UPDATE SET DivisionId = source.DivisionId,
                           DivisionName = source.DivisionName,
                           ModifiedDate = source.ModifiedDate,
                           ModifiedByEmployeeKey = source.ModifiedByEmployeeKey,
                           SalaryBudget = source.SalaryBudget,
                           FteBudget = source.FteBudget,
                           SuppliesBudget = source.SuppliesBudget,
                           FloorSpaceBudget = source.FloorSpaceBudget,
                           MaxEmployees = source.MaxEmployees,
                           LastReviewCycle = source.LastReviewCycle,
                           StartTime = source.StartTime
            WHEN NOT MATCHED THEN
                INSERT
                (
                    DivisionId,
                    DivisionName,
                    CreatedByEmployeeKey,
                    ModifiedByEmployeeKey,
                    SalaryBudget,
                    FteBudget,
                    SuppliesBudget,
                    FloorSpaceBudget,
                    MaxEmployees,
                    LastReviewCycle,
                    StartTime
                )
                VALUES
                (source.DivisionId, source.DivisionName, source.CreatedByEmployeeKey, source.ModifiedByEmployeeKey,
                 source.SalaryBudget, source.FteBudget, source.SuppliesBudget, source.FloorSpaceBudget, source.MaxEmployees,
                 source.LastReviewCycle, source.StartTime)
            OUTPUT Inserted.DivisionKey;&quot;;

        //update audit column
        var utcNow = DateTime.UtcNow;
        division.CreatedDate = utcNow;
        division.ModifiedDate = utcNow;

        return DbConnector.Scalar&lt;int&gt;(sql, division).Execute();
    }
}
</code></pre>

<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>EF Core doesn't directly support an atomic upsert, so often a read must proceed the update.</p>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;Division&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public UpsertScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public Division GetByKey(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Find(divisionKey);
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //check to see if the row already exists
            var actual = context.Division.Where(x =&gt; x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                context.Division.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Department = division.Department;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                context.Division.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = context.Division.Find(division.DivisionKey);

                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Department = division.Department;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }
}
</code></pre>

<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>EF Core doesn't directly support an atomic upsert, so often a read must proceed the update.</p>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;Division&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public UpsertScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public Division? GetByKey(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Divisions.Find(divisionKey);
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //check to see if the row already exists
            var actual = context.Divisions.Where(x =&gt; x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                context.Divisions.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Departments = division.Departments;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var context = CreateDbContext())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                context.Divisions.Add(division);
                context.SaveChanges();
                return division.DivisionKey;
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = context.Divisions.Find(division.DivisionKey);
                if (actual == null)
                    throw new DataException(&quot;Record not found&quot;);

                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.Departments = division.Departments;
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                context.SaveChanges();
                return actual.DivisionKey;
            }
        }
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;Division&gt;
{
    public Division GetByKey(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d =&gt; d.DivisionKey == divisionKey).Single();
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var db = new OrmCookbook())
        {
            //check to see if the row already exists
            var actual = db.Division.Where(x =&gt; x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                return db.InsertWithInt32Identity(division);
            }
            else //Update
            {
                //Set fields manually so we don't overwrite CreatedBy/CreatedDate
                db.Division
                    .Where(d =&gt; d.DivisionKey == actual.DivisionKey)
                    .Set(d =&gt; d.DivisionId, division.DivisionId)
                    .Set(d =&gt; d.DivisionName, division.DivisionName)
                    .Set(d =&gt; d.FloorSpaceBudget, division.FloorSpaceBudget)
                    .Set(d =&gt; d.FteBudget, division.FteBudget)
                    .Set(d =&gt; d.LastReviewCycle, division.LastReviewCycle)
                    .Set(d =&gt; d.MaxEmployees, division.MaxEmployees)
                    .Set(d =&gt; d.ModifiedByEmployeeKey, division.ModifiedByEmployeeKey)
                    .Set(d =&gt; d.ModifiedDate, division.ModifiedDate)
                    .Set(d =&gt; d.SalaryBudget, division.SalaryBudget)
                    .Set(d =&gt; d.StartTime, division.StartTime)
                    .Set(d =&gt; d.SuppliesBudget, division.SuppliesBudget)
                    .Update();

                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var db = new OrmCookbook())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                return db.InsertWithInt32Identity(division);
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = db.Division.Where(x =&gt; x.DivisionKey == division.DivisionKey).Single();

                //Set fields manually so we don't overwrite CreatedBy/CreatedDate
                db.Division
                    .Where(d =&gt; d.DivisionKey == actual.DivisionKey)
                    .Set(d =&gt; d.DivisionId, division.DivisionId)
                    .Set(d =&gt; d.DivisionName, division.DivisionName)
                    .Set(d =&gt; d.FloorSpaceBudget, division.FloorSpaceBudget)
                    .Set(d =&gt; d.FteBudget, division.FteBudget)
                    .Set(d =&gt; d.LastReviewCycle, division.LastReviewCycle)
                    .Set(d =&gt; d.MaxEmployees, division.MaxEmployees)
                    .Set(d =&gt; d.ModifiedByEmployeeKey, division.ModifiedByEmployeeKey)
                    .Set(d =&gt; d.ModifiedDate, division.ModifiedDate)
                    .Set(d =&gt; d.SalaryBudget, division.SalaryBudget)
                    .Set(d =&gt; d.StartTime, division.StartTime)
                    .Set(d =&gt; d.SuppliesBudget, division.SuppliesBudget)
                    .Update();

                return actual.DivisionKey;
            }
        }
    }
}
</code></pre>

<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>LLBLGen Pro doesn't support an atomic upsert, so a read must proceed the update.</p>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;DivisionEntity&gt;
{
    public DivisionEntity? GetByKey(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.FirstOrDefault(d =&gt; d.DivisionKey == divisionKey);
        }
    }


    public int UpsertByName(DivisionEntity division)
    {
        if(division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        // LLBLGen Pro doesn't support an atomic upsert statement. Instead we'll simply fetch
        // the entity with the name specified. If it's there, we'll get a filled entity instance
        // which we'll update with the new values (and only the values we then change will be updated
        // in the database), or it'll be a new one, which we'll set to new values and it'll be 
        // inserted. 

        using(var adapter = new DataAccessAdapter())
        {
            var toSave = adapter.FetchNewEntity&lt;DivisionEntity&gt;(
                                    new RelationPredicateBucket(DivisionFields.DivisionName.Equal(division.DivisionName)));

            // now we'll just set fields and persist it again. If the above fetch returned an existing entity
            // it'll be updated, otherwise it'll be inserted.
            // If the above fetch returned an existing entity, only the fields which changed are updated
            // in the update query.
            toSave.CreatedDate = DateTime.UtcNow;
            toSave.ModifiedDate = DateTime.UtcNow;
            toSave.CreatedByEmployeeKey = division.CreatedByEmployeeKey;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.DivisionId = division.DivisionId;
            toSave.DivisionName = division.DivisionName;
            toSave.FloorSpaceBudget = division.FloorSpaceBudget;
            toSave.FteBudget = division.FteBudget;
            toSave.LastReviewCycle = division.LastReviewCycle;
            toSave.MaxEmployees = division.MaxEmployees;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.ModifiedDate = division.ModifiedDate;
            toSave.SalaryBudget = division.SalaryBudget;
            toSave.StartTime = division.StartTime;
            toSave.SuppliesBudget = division.SuppliesBudget;
            adapter.SaveEntity(toSave);
            return toSave.DivisionKey;
        }
    }


    public int UpsertByPrimaryKey(DivisionEntity division)
    {
        if(division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        // LLBLGen Pro doesn't support an atomic upsert statement. Instead we'll simply fetch
        // the entity with the name specified. If it's there, we'll get a filled entity instance
        // which we'll update with the new values (and only the values we then change will be updated
        // in the database), or it'll be a new one, which we'll set to new values and it'll be 
        // inserted. 

        using(var adapter = new DataAccessAdapter())
        {
            var toSave = adapter.FetchNewEntity&lt;DivisionEntity&gt;(
                                    new RelationPredicateBucket(DivisionFields.DivisionKey.Equal(division.DivisionKey)));

            // now we'll just set fields and persist it again. If the above fetch returned an existing entity
            // it'll be updated, otherwise it'll be inserted.
            // If the above fetch returned an existing entity, only the fields which changed are updated
            // in the update query.
            toSave.CreatedDate = DateTime.UtcNow;
            toSave.ModifiedDate = DateTime.UtcNow;
            toSave.CreatedByEmployeeKey = division.CreatedByEmployeeKey;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.DivisionId = division.DivisionId;
            toSave.DivisionName = division.DivisionName;
            toSave.FloorSpaceBudget = division.FloorSpaceBudget;
            toSave.FteBudget = division.FteBudget;
            toSave.LastReviewCycle = division.LastReviewCycle;
            toSave.MaxEmployees = division.MaxEmployees;
            toSave.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
            toSave.ModifiedDate = division.ModifiedDate;
            toSave.SalaryBudget = division.SalaryBudget;
            toSave.StartTime = division.StartTime;
            toSave.SuppliesBudget = division.SuppliesBudget;
            adapter.SaveEntity(toSave);
            return toSave.DivisionKey;
        }
    }
}
</code></pre>

<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>NHibernate doesn't directly support an atomic upsert, so often a read must proceed the update.</p>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;Division&gt;
{
    readonly ISessionFactory m_SessionFactory;

    public UpsertScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public Division GetByKey(int divisionKey)
    {
        using (var session = m_SessionFactory.OpenSession())
            return session.Get&lt;Division&gt;(divisionKey);
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var session = m_SessionFactory.OpenSession())
        {
            //check to see if the row already exists
            var actual = session.Query&lt;Division&gt;().Where(x =&gt; x.DivisionName == division.DivisionName).SingleOrDefault();
            if (actual == null) //Insert
            {
                session.Save(division);
                session.Flush();
                return division.DivisionKey;
            }
            else //Update
            {
                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                session.Flush();
                return actual.DivisionKey;
            }
        }
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        //update audit column
        division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;

        using (var session = m_SessionFactory.OpenSession())
        {
            //If DivisionKey is zero, we know this is a new row
            if (division.DivisionKey == 0) //Insert
            {
                session.Save(division);
                session.Flush();
                return division.DivisionKey;
            }
            else //Update
            {
                //This wouldn't be necessary if we were replacing all columns.
                var actual = session.Get&lt;Division&gt;(division.DivisionKey);

                //Copy manually so we don't overwrite CreatedBy/CreatedDate
                actual.DivisionId = division.DivisionId;
                actual.DivisionName = division.DivisionName;
                actual.FloorSpaceBudget = division.FloorSpaceBudget;
                actual.FteBudget = division.FteBudget;
                actual.LastReviewCycle = division.LastReviewCycle;
                actual.MaxEmployees = division.MaxEmployees;
                actual.ModifiedByEmployeeKey = division.ModifiedByEmployeeKey;
                actual.ModifiedDate = division.ModifiedDate;
                actual.SalaryBudget = division.SalaryBudget;
                actual.StartTime = division.StartTime;
                actual.SuppliesBudget = division.SuppliesBudget;
                session.Flush();
                return actual.DivisionKey;
            }
        }
    }
}
</code></pre>

<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class UpsertScenario : BaseRepository&lt;Division, SqlConnection&gt;,
    IUpsertScenario&lt;Division&gt;
{
    public UpsertScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public Division? GetByKey(int divisionKey)
    {
        return Query(e =&gt; e.DivisionKey == divisionKey).FirstOrDefault();
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        return Merge&lt;int&gt;(division, qualifiers: Field.From(&quot;DivisionName&quot;));
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        return Merge&lt;int&gt;(division, qualifiers: Field.From(&quot;DivisionKey&quot;));
    }
}
</code></pre>

<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class UpsertScenario : IUpsertScenario&lt;Division&gt;
{
    private IDbConnectionFactory _dbConnectionFactory;

    public UpsertScenario(IDbConnectionFactory dbConnectionFactory)
    {
        this._dbConnectionFactory = dbConnectionFactory;
    }

    public Division GetByKey(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.SingleById&lt;Division&gt;(divisionKey);
        }
    }

    public int UpsertByName(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        if (division.CreatedDate == default(DateTime))
            division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var existing = db.Single&lt;Division&gt;(c =&gt; c.DivisionName == division.DivisionName);
            if (existing != null)
            {
                division.PopulateWith(existing.PopulateWithNonDefaultValues(division));
            }
            db.Save(division);
        }
        
        return division.Id;
    }

    public int UpsertByPrimaryKey(Division division)
    {
        if (division == null)
            throw new ArgumentNullException(nameof(division), $&quot;{nameof(division)} is null.&quot;);

        if (division.CreatedDate == default(DateTime))
            division.CreatedDate = DateTime.UtcNow;
        division.ModifiedDate = DateTime.UtcNow;
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(division);
        }

        return division.Id;
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
